SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[WeeklyPropTable] AS
BEGIN
SET NOCOUNT ON 
DECLARE @dbname  VARCHAR(50)
 DECLARE @FundID  VARCHAR(10)
 DECLARE @NAVDate DATETIME
 
 

 SET @dbname  = 'FSFundModel'
 SET @FundID  = 'FSIC'
 SET @NAVDate = (SELECT	MAX(e.NAVDate)  FROM FSFundModel.Reporting.Export e WHERE e.FundId = @FundID )
 DECLARE @ExportId INT = (SELECT MAX(e.ExportId)
            FROM    FSFundModel.Reporting.Export e
            WHERE   e.FundId  = @FundID
            AND     e.NAVDate = @NAVDate)
 DECLARE @results TABLE(
 issuerName VARCHAR(MAX	)
 , instrType VARCHAR(MAX)
 , IssuerDesc VARCHAR(MAX	)
 , mktval VARCHAR(MAX)
 --, RateType VARCHAR(MAX	)
 --, Spread	VARCHAR(MAX	)
 --, FLOOR	VARCHAR(MAX	)

 )
 --GOTO ScriptRunner ScriptExecutes:
 
 INSERT INTO @results
         --( issuerName ,
         --  instrType ,
         --  IssuerDesc ,
         --  mktval
         --)
 SELECT   

 iss.IssuerName, id.InstTypeDescr + ': ' + REPLACE(REVERSE(RIGHT(REVERSE(i.InstrName), LEN(i.InstrName)-CHARINDEX( ' ,',REVERSE(i.InstrName)))), iss.IssuerName+', ', ''),iss.IssuerDescr, 
 
 LEFT(CONVERT(VARCHAR(50),CAST(SUM(a.MktVal)/1000000 AS MONEY),1), LEN(CONVERT(VARCHAR(50),CAST(SUM(a.MktVal)/1000000 AS MONEY),1))-1) AS MktVal



 
 FROM       FSFundModel.Reporting.Asset a
            INNER JOIN FSFundModel.Reporting.Instrument i
            ON         i.ExportId     = a.ExportId
            AND        a.InstrumentId = i.InstrumentId
            
			INNER JOIN  ReportingScripts.Live.AssetHelper(@ExportId) ah ON ah.AssetIntrnlId = a.AssetIntrnlId
            --LEFT JOIN FSFundModel.Reporting.FundCreditInstrument fci
            --ON         i.ExportId               = fci.ExportId
            --AND        i.InstrumentId           = fci.InstrumentId
            --AND        a.FundCreditInstrumentId = fci.FundCreditInstrumentId
            
            INNER JOIN FSFundModel.Reporting.Issuer iss
            ON         iss.ExportId = a.ExportId
            AND        i.IssuerId   = iss.IssuerId
            
            INNER JOIN FSFundModel.Reporting.InstrumentDefinition id
            ON         i.InstDefId = id.InstDefId
            AND        id.ExportId = a.ExportId
            
            INNER JOIN FSFundModel.Reporting.FundIssuer fi
            ON         a.ExportId  = fi.ExportId
            AND        fi.IssuerId = iss.IssuerId
            
            INNER JOIN FSFundModel.Reporting.Portfolio p
            ON         a.ExportId    = p.ExportId
            AND        a.PortfolioId = p.PortfolioId
            
            INNER JOIN FSFundModel.Reporting.Fund f
            ON         a.ExportId = f.ExportId
            
          
 WHERE      a.ExportId                = @ExportId
            
          AND ah.[NBSReason] = 'Proprietary'
		  AND a.IsActiveEndOfDay = 1
		  
GROUP BY iss.IssuerDescr, id.InstTypeDescr, i.InstrName, iss.IssuerName
ORDER BY iss.IssuerName ASC   	


DECLARE @FinalT TABLE(
issuerName VARCHAR(MAX	)
 , instrType VARCHAR(MAX)
 , IssuerDesc VARCHAR(MAX	)
 , mktval VARCHAR(MAX)
 )

 INSERT INTO @FinalT 
SELECT DISTINCT r.issuerName, STUFF((
		SELECT ',o ' + tmp.instrType FROM (SELECT r.issuerName, r.instrType 
		FROM  @results r) AS tmp
		WHERE tmp.issuerName = r.issuerName
		FOR XML PATH ('')
		), 1,1,'')
		
		
		, r.IssuerDesc, 

		STUFF((
		SELECT ',$' + tmp.mktval FROM (SELECT r.issuerName, r.instrType, r.mktval
		FROM  @results r) AS tmp
		WHERE tmp.issuerName = r.issuerName
		--AND tmp.instrType = r.instrType
		FOR XML PATH ('')
		), 1,1,'')
        
FROM @results r



--UPDATE ft
--SET ft.instrType = COALESCE(r.instrType + ', ', r.instrType) 

--FROM  @FinalT ft
--INNER JOIN @results r ON ft.issuerName = r.issuerName

--SELECT * FROM  @results

SELECT  issuerName ,
        instrType ,
        IssuerDesc ,
        mktval FROM  @FinalT
END

GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Weekly', 'SCHEMA', N'dbo', 'PROCEDURE', N'WeeklyPropTable', NULL, NULL
GO
